VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "ClsSQL"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

Public cadena As String
Private s() As String
Private J() As String
Private F() As String
Private w() As String
Private o() As String
Private d() As String
Private g() As String

Private Sub Class_Initialize()
    Me.cadena = ""
    ReDim s(0) As String
    ReDim J(0, 0) As String
    ReDim F(0) As String
    ReDim w(0) As String
    ReDim o(0) As String
    ReDim d(0) As String
    ReDim g(0) As String
    
End Sub

Public Sub reset()
    Me.cadena = ""
    ReDim s(0) As String
    ReDim J(0, 0) As String
    ReDim F(0) As String
    ReDim w(0) As String
    ReDim o(0) As String
    ReDim d(0) As String
    ReDim g(0) As String
End Sub

Public Sub setCampos(cad As Variant)
   Dim i As Integer
   ReDim s(UBound(cad)) As String
   i = 0
   While i <= UBound(cad)
      s(i) = cad(i)
      i = i + 1
   Wend
End Sub
Public Sub setJoin(cad As Variant)
   Dim i As Integer
   ReDim J(UBound(cad), 2) As String
   i = 0
   While i <= UBound(cad)
      J(i, 0) = cad(i)(0)
      J(i, 1) = cad(i)(1)
      J(i, 2) = cad(i)(2)
      i = i + 1
   Wend
End Sub
Public Sub setTablas(cad As Variant)
   Dim i As Integer
   ReDim F(UBound(cad)) As String
   i = 0
   While i <= UBound(cad)
      F(i) = cad(i)
      i = i + 1
   Wend
End Sub
Public Sub setCondiciones(cad As Variant)
    Dim i As Integer
    If UBound(cad) >= 0 Then
        ReDim w(UBound(cad)) As String
        i = 0
        While i <= UBound(cad)
           w(i) = cad(i)
           i = i + 1
        Wend
    End If
End Sub
Public Sub setOrden(cad As Variant)
    Dim i As Integer
    ReDim o(UBound(cad)) As String
    i = 0
    While i <= UBound(cad)
        o(i) = cad(i)
        i = i + 1
    Wend
End Sub
Public Sub setDatos(cad As Variant)
   Dim i As Integer
   ReDim d(UBound(cad)) As String
   i = 0
   While i <= UBound(cad)
      d(i) = cad(i)
      i = i + 1
   Wend
End Sub
Public Sub setAgrupaPor(cad As Variant)
   Dim i As Integer
   ReDim g(UBound(cad)) As String
   i = 0
   While i <= UBound(cad)
      g(i) = cad(i)
      i = i + 1
   Wend
End Sub
Public Sub setUnion(otroSQL As String)
    Me.cadena = "(" & Me.cadena & ") UNION (" & otroSQL & ")"
End Sub
'metodo para agregarCampos
Public Sub agregarCampo(campo As String)
    Dim campoTemp As Variant
    campoTemp = concatenaVectores(F, Array(campo))
    ReDim F(UBound(campoTemp)) As String
    Dim i As Integer
    i = 0
    While i <= UBound(campoTemp)
       s(i) = campoTemp(i)
       i = i + 1
    Wend
End Sub
'metodo para agregar tablas
Public Sub agregarTabla(tabla As String)
    Dim tablaTemp As Variant
    tablaTemp = concatenaVectores(F, Array(tabla))
    ReDim F(UBound(tablaTemp)) As String
    Dim i As Integer
    i = 0
    While i <= UBound(tablaTemp)
       F(i) = tablaTemp(i)
       i = i + 1
    Wend
End Sub
'metodo para agregar condiciones
Public Sub agregarCondicion(condicion As String)
    Dim condicTemp As Variant
    If w(0) = "" Then
        condicTemp = Array(condicion)
    Else
        condicTemp = concatenaVectores(w, Array(condicion))
    End If
    ReDim w(UBound(condicTemp)) As String
    Dim i As Integer
    i = 0
    While i <= UBound(condicTemp)
        w(i) = condicTemp(i)
        i = i + 1
    Wend
End Sub
'METODOS PARA CONSTRUCCION DE SENTENCIAS SQL
Public Function sSelect()
    Me.cadena = "SELECT " & masCampos & _
                " FROM " & masTablas
    If J(0, 0) <> "" Then
        Me.cadena = Me.cadena & masJoins
    End If
    If w(0) <> "" Then
        Me.cadena = Me.cadena & " WHERE " & masCondiciones
    End If
    If g(0) <> "" Then
        Me.cadena = Me.cadena & " GROUP BY " & masAgrupaPor
    End If
    If o(0) <> "" Then
        Me.cadena = Me.cadena & " ORDER BY " & masOrden
    End If
    sSelect = Me.cadena
End Function
Public Function sInsert()
    Me.cadena = "INSERT INTO " & masTablas & _
                " ( " & masCampos & " ) VALUES" & _
                " ( " & masDatos & " ) "
    sInsert = Me.cadena
End Function
Public Function sUpdate()
    Me.cadena = "UPDATE " & masTablas & _
                " SET " & mascampos_Datos
    If w(0) <> "" Then
        Me.cadena = Me.cadena & " WHERE " & masCondiciones
    End If
    sUpdate = Me.cadena
End Function
Public Function sDelete()
    Me.cadena = "DELETE FROM " & masTablas
    If w(0) <> "" Then
        Me.cadena = Me.cadena & " WHERE " & masCondiciones
    End If
    sDelete = Me.cadena
End Function
Public Function sAddNew()
    'crea un sql SIN DATOS para poder hacer un AddNew en el recordset y asi recuperar los datos insertados
    Me.cadena = "SELECT TOP 0 " & masCampos & _
                " FROM " & masTablas
    If J(0, 0) <> "" Then
        Me.cadena = Me.cadena & masJoins
    End If
    If w(0) <> "" Then
        Me.cadena = Me.cadena & " WHERE " & masCondiciones
    End If
    If o(0) <> "" Then
        Me.cadena = Me.cadena & " ORDER BY " & masOrden
    End If
    If g(0) <> "" Then
        Me.cadena = Me.cadena & " GROUP BY " & masAgrupaPor
    End If
    sAddNew = Me.cadena
End Function
Public Function sSelectTipoCambio(campoFecha As String, campoMonto As String, camposNormales As Variant, Optional posicion As String = "")
    Dim nuevosCampos() As String
    ReDim nuevosCampos(UBound(s) + 2) As String
    Dim i, k As Integer
    Dim insertados As Boolean
    insertados = False
    
    If posicion <> "" Then
        If IsNumeric(posicion) Then
            'se coloca antes del campo en la posicion "posicion"
            Dim pos As Integer
            pos = Conversion.CInt(posicion)
            i = 0
            k = 0
            While i < UBound(nuevosCampos)
                If pos <= i And Not insertados Then
                    nuevosCampos(i) = tpc_cambio
                    i = i + 1
                    nuevosCampos(i) = campoMonto & "/" & tpc_cambio
                    i = i + 1
                    insertados = True
                End If
                nuevosCampos(i) = s(k)
                i = i + 1
                k = k + 1
            Wend
        Else
            'se coloca antes de un campo con el nombre "posicion"
            i = 0
            k = 0
            
            While i <= UBound(nuevosCampos)
                If posicion = s(k) And Not insertados Then
                    nuevosCampos(i) = tpc_cambio
                    i = i + 1
                    nuevosCampos(i) = campoMonto & "/" & tpc_cambio
                    i = i + 1
                    insertados = True
                End If
                nuevosCampos(i) = s(k)
                i = i + 1
                If (k < UBound(s)) Then
                    k = k + 1
                Else
                    nuevosCampos(i) = tpc_cambio
                    i = i + 1
                    nuevosCampos(i) = campoMonto & "/" & tpc_cambio
                    i = i + 1
                    insertados = True
                End If
            Wend
        End If
    Else
        'se colocan al ultimo
        i = 0
        
        While i <= UBound(s)
            nuevosCampos(i) = s(i)
            i = i + 1
        Wend
        nuevosCampos(i) = tpc_cambio
        i = i + 1
        nuevosCampos(i) = campoMonto & "/" & tpc_cambio
        i = i + 1
    End If
    
    'primero construye el sql original (pero sin order)
    Dim sqlOriginal As String
    sqlOriginal = "SELECT " & Strings.Join(camposNormales, ",") & " FROM " & masTablas
    If w(0) <> "" Then
        sqlOriginal = sqlOriginal & " WHERE " & masCondiciones
    End If
    If g(0) <> "" Then
        sqlOriginal = sqlOriginal & " GROUP BY " & masAgrupaPor
    End If
    
    Dim sqlBuscaTc As String
    
    sqlBuscaTc = "SELECT " & Strings.Join(camposNormales, ",") & ",max(" & tpc_fecha & ") as fechacambio" & _
                 " FROM (" & sqlOriginal & ") as consultaOriginal" & _
                 " LEFT JOIN " & tpc & " ON " & tpc_fecha & "<=" & campoFecha & _
                 " GROUP BY " & Strings.Join(camposNormales, ",")
        
    Me.cadena = "SELECT " & Strings.Join(nuevosCampos, ",") & _
                " FROM (" & sqlBuscaTc & ") as buscaTc," & tpc & _
                " WHERE buscaTc.fechacambio=" & tpc_fecha
    If o(0) <> "" Then
        Me.cadena = Me.cadena & " ORDER BY " & masOrden
    End If
    sSelectTipoCambio = Me.cadena
End Function

'Metodos para ayuda en la construccion de sqlqueries
Private Function masCampos() As String
    Dim cadTemp As String
    Dim i As Integer
    If s(0) <> "" Then
        For i = 0 To UBound(s)
            cadTemp = cadTemp & s(i)
            If i < UBound(s) Then
                cadTemp = cadTemp & ","
            End If
        Next i
    Else
        cadTemp = cadTemp & "*"
    End If
    masCampos = cadTemp
End Function
Private Function masJoins() As String
    Dim cadTemp As String
    Dim i As Integer
    For i = 0 To UBound(J)
        cadTemp = cadTemp & " " & J(i, 0) & " "
        If UBound(J) > 1 Then
            cadTemp = cadTemp & "( "
        End If
        cadTemp = cadTemp & J(i, 1) & " "
    Next i
    For i = 0 To UBound(J)
        If UBound(J) > 1 Then
            cadTemp = cadTemp & " ) "
        End If
        cadTemp = cadTemp & " on " & J(UBound(J) - i, 2)
    Next i
    masJoins = cadTemp
End Function
Private Function masTablas() As String
    Dim cadTemp As String
    Dim i As Integer
    For i = 0 To UBound(F)
        cadTemp = cadTemp & F(i)
        If i < UBound(F) Then
            cadTemp = cadTemp & ","
        End If
    Next i
    masTablas = cadTemp
End Function
Private Function masCondiciones() As String
    Dim cadTemp As String
    Dim i As Integer
    For i = 0 To UBound(w)
        cadTemp = cadTemp & w(i)
        If i < UBound(w) Then
            cadTemp = cadTemp & " AND "
        End If
    Next i
    masCondiciones = cadTemp
End Function
Private Function masOrden() As String
    Dim cadTemp As String
    Dim i As Integer
    For i = 0 To UBound(o)
        cadTemp = cadTemp & o(i)
        If i < UBound(o) Then
            cadTemp = cadTemp & ","
        End If
    Next i
    'cadTemp = cadTemp & O_t
    masOrden = cadTemp
End Function
Private Function masDatos() As String
    Dim cadTemp As String
    Dim i As Integer
    For i = 0 To UBound(d)
        If Strings.UCase(d(i)) = "NULL" Then
            cadTemp = cadTemp & "NULL"
        Else
            Select Case dbTipos.Item(s(i))
                Case 1: 'numero
                    If (Strings.LenB(d(i)) > 0) Then
                        cadTemp = cadTemp & d(i)
                    Else
                        cadTemp = cadTemp & "0"
                    End If
                Case 2: 'texto
                    cadTemp = cadTemp & "'" & Strings.Replace(d(i), "'", "''") & "'"
                Case 3: 'bit
                    If d(i) Then
                        Select Case coneXion
                            Case "SQLSERVER":
                                cadTemp = cadTemp & "-1"
                            Case "ACCESS":
                                cadTemp = cadTemp & "1"
                        End Select
                    Else
                        cadTemp = cadTemp & "0"
                    End If
                Case 4: 'fecha
                    cadTemp = cadTemp & "'" & CambiaFecha(d(i), False) & "'"
            End Select
        End If
        If i < UBound(d) Then
            cadTemp = cadTemp & ","
        End If
    Next i
    masDatos = cadTemp
End Function
Private Function mascampos_Datos() As String
    Dim cadTemp As String
    Dim i As Integer
    For i = 0 To UBound(s)
        cadTemp = cadTemp & s(i) & " = "     'solo hace update para la primera tabla
        If Strings.UCase(d(i)) = "NULL" Then
            cadTemp = cadTemp & "null"
        Else
            Select Case dbTipos.Item(s(i))
                Case 1: 'numero
                    If (Strings.Len(d(i)) > 0) Then
                        cadTemp = cadTemp & d(i)
                    Else
                        cadTemp = cadTemp & "0"
                    End If
                Case 2: 'texto
                    cadTemp = cadTemp & "'" & Strings.Replace(d(i), "'", "''") & "'"
                Case 3: 'bit
                    If d(i) Then
                        Select Case coneXion
                            Case "SQLSERVER":
                                cadTemp = cadTemp & "-1"
                            Case "ACCESS":
                                cadTemp = cadTemp & "1"
                        End Select
                    Else
                        cadTemp = cadTemp & "0"
                    End If
                Case 4: 'fecha
                    cadTemp = cadTemp & "'" & CambiaFecha(d(i), False) & "'"
            End Select
        End If
        If i < UBound(s) Then
            cadTemp = cadTemp & ","
        End If
    Next i
    mascampos_Datos = cadTemp
End Function
Private Function masAgrupaPor() As String
    Dim cadTemp As String
    Dim i As Integer
    For i = 0 To UBound(g)
        cadTemp = cadTemp & g(i)
        If i < UBound(g) Then
            cadTemp = cadTemp & ","
        End If
    Next i
    masAgrupaPor = cadTemp
End Function
Public Function numCampos() As Integer
    numCampos = UBound(s) + 1
End Function
Public Function getDato(numCampo As Integer)
    getDato = d(numCampo)
End Function
Public Function getCampo(numCampo As Integer)
    getCampo = s(numCampo)
End Function
Public Function getTabla(numTabla As Integer)
    getTabla = F(numTabla)
End Function

Public Function getCamposUpdate() As Variant
    getCamposUpdate = VectorExtract(s, 1)
End Function
Public Function getDatosUpdate() As Variant
    getDatosUpdate = VectorExtract(d, 1)
End Function
